package meminin.utilities.dbconn;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class MySqlConnector {

    public Connection getConnect() {
        return connect;
    }

    public ResultSet executeRead(String query) {
        try {
            Statement stat = connect.createStatement();
            return stat.executeQuery(query);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Integer executeUpdate(String query) {
        try {
            Statement stat = connect.createStatement();
            return stat.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    Connection connect = null;
    Statement statement = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    String dbName;
    String currentTable;
    int col;

    public MySqlConnector(String hostName, String dbName, String user, String password) {
        try {
            this.dbName = dbName;
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://"+hostName+"/" + dbName + "?user=" + user + "&password=" + password);
            statement = connect.createStatement();
        } catch (Exception ex) {
            Logger.getLogger(MySqlConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String getCurrTable() {
        return dbName + "." + currentTable;
    }

    public void createTable(String tableName) {
        try {
            currentTable = tableName;
            if (checkTableExist()) {
                statement.execute("drop table " + tableName + ";");
            }
            statement.execute("CREATE TABLE IF NOT EXISTS " + tableName + " (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID));");
        } catch (Exception ex) {
            Logger.getLogger(MySqlConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void addColumn(String column, String type, String format) {
        try {
            statement.executeUpdate("ALTER TABLE " + currentTable + " ADD " + column + " " + type + " " + format + " NOT NULL;");
            col++;
            System.out.println("Adding column " + column);
        } catch (Exception ex) {
            System.out.println("error add column: " + ex.getMessage());
        }
    }

    public void prepareInsert() {
        try {
            String str = "insert into " + currentTable + " values (default,";
            for (int i = 0; i < col; i++) {
                str = str.concat(" ?,");
            }
            str = str.substring(0, str.length() - 1).concat(");");
            preparedStatement = connect.prepareStatement(str);
        } catch (Exception ex) {
            System.out.println("error prepare");
        }
    }

    public void insertRow(String[] row) {
        try {
            for (int i = 0; i < row.length; i++) {
                preparedStatement.setObject(i + 1, row[i]);
            }
            int a = preparedStatement.executeUpdate();
            System.out.println("Insert row ");
        } catch (Exception ex) {
            System.out.println("Error insert row: " + ex.getMessage());
        }
    }

    public void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

            if (connect != null) {
                connect.close();
            }
        } catch (Exception e) {
        }
    }

    public boolean checkTableExist() {
        boolean exist = false;
        try {
            statement.executeQuery("select * from " + currentTable);
            exist = true;
        } catch (Exception e) {
        }
        return exist;
    }

    public ResultSet getTableData() {
        try {
            resultSet = statement.executeQuery("select * from " + currentTable);
        } catch (SQLException ex) {
            Logger.getLogger(MySqlConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultSet;
    }
}
